package dyyx.util;

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public abstract class JdbcUtil {

	public static void main(String[] args) throws Exception {		
		Connection cn = getConn();		
		// autoCommit false
		System.out.println("cn="+cn+",autoCommit="+cn.getAutoCommit());	
		String sql = "select distinct name from system.databases";
		List<String> dbs = JdbcUtil.queryStringList(cn, sql);
		// default, pass, system
		System.out.println("dbs="+dbs);	
		
		close(cn);
			
	}
	
	public static String getDriver(String url)throws SQLException{
		Driver driver = DriverManager.getDriver(url);
		return driver.getClass()+","+driver;
	}
	
	public static Connection getConn()throws Exception{
		String driver = ConfigUtil.get("driver");
		String url = ConfigUtil.get("url");
		String user = ConfigUtil.get("user");
		String pwd = ConfigUtil.get("pwd");
		
		if(CommUtil.isBlank(user)){
			user = null;
		}
		if(CommUtil.isBlank(pwd)){
			pwd = null;
		}
		
		Class.forName(driver);

		return DriverManager.getConnection(url,user,pwd);
	}
	

	public static void close(AutoCloseable obj) {
		if (obj == null) {
			return;
		}
		try {
			obj.close();
		} catch (Throwable e) {

		}
	}

	public static ResultSetMeta getResultSetMataData(Connection connection, String sql) throws SQLException {

		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = connection.createStatement();
			rs = stmt.executeQuery(sql);
			ResultSetMetaData rsmd = rs.getMetaData();
			return new ResultSetMeta(rsmd);
		} finally {
			close(rs);
			close(stmt);
		}

	}
	
	public static Object queryAsObject(Connection connection, String sql)throws SQLException {
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = connection.createStatement();
			stmt.setMaxRows(1);
			rs = stmt.executeQuery(sql);
			while(rs.next()){
				return rs.getObject(1);
			}
			return null;
		} finally {
			close(rs);
			close(stmt);
		}
	}
	
	public static List<Map<String,Object>> queryList(Connection connection, String sql)throws SQLException {
		return queryList(connection,sql,0);
	}

	
	public static List<Map<String,Object>> queryList(Connection connection, String sql,int maxRows)throws SQLException {
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = connection.createStatement();
			if(maxRows>0){
			    stmt.setMaxRows(maxRows);
			}
			rs = stmt.executeQuery(sql);
			List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
			
			ResultSetMeta rsmd = JdbcUtil.getResultSetMataData(connection, sql);
			
			Map<String,Object> row = null;
			int columnCount = rsmd.columnCount;
			List<ColumnInfo> columns = rsmd.columns;
			
			while(rs.next()){				
				row = new HashMap<String,Object>();
				for(int i=0;i < columnCount;i++){
					row.put(columns.get(i).columnName, rs.getObject(i+1));
				}	
				list.add(row);
			}
			return list;
		} finally {
			close(rs);
			close(stmt);
		}
	}
	
	public static List<String> queryStringList(Connection connection, String sql)throws SQLException {
		return queryStringList(connection,sql,0);
	}

	
	public static List<String> queryStringList(Connection connection, String sql,int maxRows)throws SQLException {
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = connection.createStatement();
			if(maxRows>0){
			    stmt.setMaxRows(maxRows);
			}
			rs = stmt.executeQuery(sql);			
			List<String> list = new ArrayList<String>();				
			while(rs.next()){
				list.add(rs.getString(1));
			}
			return list;
		} finally {
			close(rs);
			close(stmt);
		}
	}
	
	
	public static String queryAsString(Connection connection, String sql)throws SQLException {
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = connection.createStatement();
			stmt.setMaxRows(1);

			rs = stmt.executeQuery(sql);
			while(rs.next()){
				return rs.getString(1);
			}
			return null;
		} finally {
			close(rs);
			close(stmt);
		}
	}
	
	public static Integer queryAsInteger(Connection connection, String sql)throws SQLException {
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = connection.createStatement();
			stmt.setMaxRows(1);

			rs = stmt.executeQuery(sql);
			while(rs.next()){
				return rs.getInt(1);
			}
			return null;
		} finally {
			close(rs);
			close(stmt);
		}
	}
	
	public static Long queryAsLong(Connection connection, String sql)throws SQLException {
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = connection.createStatement();
			stmt.setMaxRows(1);

			rs = stmt.executeQuery(sql);
			while(rs.next()){
				return rs.getLong(1);
			}
			return null;
		} finally {
			close(rs);
			close(stmt);
		}
	}
	
	
	public static void query(QueryInfo info)throws Exception {
		String sql =  info.sql;
		int maxRows = info.maxRows;
		
		Connection connection = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			connection = getConn();
			stmt = connection.createStatement();
			if(maxRows>0){
				stmt.setMaxRows(maxRows);
			}		
			rs = stmt.executeQuery(sql);
			ResultSetMetaData rsmd = rs.getMetaData();
			ResultSetMeta rsm = new ResultSetMeta(rsmd);
			
			int columnCount = rsm.columnCount;
			
			info.resultSetMeta = rsm;
			List list = new ArrayList();
			Object[] row = null;
			
			while(rs.next()){
				row = new Object[columnCount];
				for(int i=0;i<columnCount;i++){
					row[i] = rs.getString(i+1);
				}
				list.add(row);
			}
			
			info.list = list;
			
			
		} finally {
			close(rs);
			close(stmt);
			close(connection);

		}
		
	}



}
